#include "dbcontrol.h"
#include <QFile>
#include <QDebug>
#include <QSqlError>
#include <QJsonDocument>
#include <QJsonObject>
#include <QCoreApplication>
#include <QDir>
#include <QRegularExpression>
#include <QtXlsx>
#include "data_mana.h"

DBControl::DBControl(QObject *parent) : QObject(parent), m_connState(false)
{

}

DBControl::~DBControl()
{
    if(db.isOpen()){
        db.close();
    }
}

bool DBControl::connectDataBase(QString host, quint16 port, QString userName, QString password, QString dbName)
{
    if(m_connState){
        return true;
    }
    db = QSqlDatabase::addDatabase("QMYSQL3");

    db.setHostName(host);
    db.setPort(port);
    db.setUserName(userName);
    db.setPassword(password);
    db.setDatabaseName(dbName);

    m_connState = db.open();
    if(m_connState){
        m_connState = true;

        m_query = QSqlQuery(db);
        qDebug() << "Conn success";
        return true;
    }
    else{
        m_connState = false;
        qDebug() << "Conn failed";
        return false;
    }
}

QVariantList DBControl::getDataBaseInfo()
{
    QVariantList sqlDataOut_List;
    // QSqlQuery query;

    QString sql = "select version()";

    m_query.exec(sql);
    while(m_query.next()){
        // qDebug() << query.value(0).toString();
        sqlDataOut_List.append(m_query.value(0));
    }

    sql = "show status like 'Threads_%'";
    m_query.exec(sql);
    while(m_query.next()){
        // qDebug() << query.value(0).toString() << query.value(1).toString();
        QString value = m_query.value(0).toString();
        value += "\t:\t";
        value += m_query.value(1).toString();

        sqlDataOut_List.append(value);
    }

    return sqlDataOut_List;
}

QString DBControl::createTables()
{
    if(!m_connState){
        return "Database not connected.";
    }

    QFile file("./sql/iscu.sql");
    if(file.open(QIODevice::ReadOnly)){
        QString sql = file.readAll();
        if(m_query.exec(sql)){
            qDebug() << "SQL file executed successfully!";

            return "SQL file executed successfully!";
        }
        else{
            qDebug() << "Failed to execute SQL file!";
            qDebug() << m_query.lastError().text();

            return "Failed to execute SQL file!";
        }
        file.close();
    }
    else{
        qDebug() << "Failed to open SQL file!";

        return "Failed to open SQL file!";
    }
}

int DBControl::modbusJson2DB()
{
    QString basePath = QCoreApplication::applicationDirPath();
    QString path = basePath + "/modbus/";
    QDir dir("./modbus");
    // 设置过滤器，只获取JSON文件
    QStringList nameFilters;
    nameFilters << "*.json";
    dir.setNameFilters(nameFilters);

    // 获取文件夹下的所有JSON文件名
    QStringList fileNames = dir.entryList();

    // 打印所有JSON文件名
    for (const QString& fileName : fileNames) {
        // qDebug() << fileName;
        handleModbusCfg(path + fileName, fileName);
    }
}

int DBControl::topicInfo2DB()
{
    QString basePath = QCoreApplication::applicationDirPath();
    QString path = basePath + "/modbus/";
    QDir dir("./modbus");
    // 设置过滤器，只获取JSON文件
    QStringList nameFilters;
    nameFilters << "*.txt";
    dir.setNameFilters(nameFilters);

    // 获取文件夹下的所有JSON文件名
    QStringList fileNames = dir.entryList();

    m_query.clear();
    // 删除数据  DELETE FROM hldkeymap WHERE orderkey >= 51;
    QString deleteCommand = "DELETE FROM hldkeymap WHERE orderkey >= 51";
    if(m_query.exec(deleteCommand))
    {
        qDebug() << "Data inserted successfully. ^_^";
    }
    else{
        qDebug() << "Failed to insert data.";
    }

    ams_orderKey = 51;
    for (const QString& fileName : fileNames) {
        // qDebug() << fileName;
        handleTopicInfo(path + fileName, fileName);
    }
}

int DBControl::handleModbusCfg(QString filePath, QString fileName)
{
    int cliNum;
    QRegularExpression regex("\\d+");
    QRegularExpressionMatchIterator matchIterator = regex.globalMatch(fileName);
    while (matchIterator.hasNext())
    {
        QRegularExpressionMatch match = matchIterator.next();
        QString number = match.captured();
        cliNum = number.toInt();
    }
    // qDebug() << "cliNum: " << cliNum;

    QFile file(filePath);

    if(!file.open(QIODevice::ReadOnly))
    {
        qDebug() << "Failed to open JSON file";
        return -1;
    }

    // 读取JSON文件内容
    QByteArray jsonData = file.readAll();
    file.close();

    // 解析JSON数据
    QJsonDocument jsonDoc = QJsonDocument::fromJson(jsonData);
    if (jsonDoc.isNull())
    {
        qDebug() << "Failed to parse JSON data";
        return -1;
    }

    // 将JSON数据转换为QJsonObject
    QJsonObject jsonObj = jsonDoc.object();
    int dataProtocol = jsonObj["DataProtocol"].toString().toInt();

    // 更新协议类型
    QString sql_Protocol = "UPDATE channelsdataprotocol SET DataProtocol = :ProtocolID WHERE WayUid = '1575876387' and ChanCode = :cliID";
    m_query.prepare(sql_Protocol);
    m_query.bindValue(":ProtocolID", dataProtocol);
    m_query.bindValue(":cliID", cliNum);
    if(!m_query.exec()){
        qDebug() << "Failed to execute SQL query";
        qDebug() << m_query.lastError().text();
        return -1;
    }

    m_query.clear();
    // Modbus配置
    QString sql_JsonCfg = "UPDATE channelsdataprotocol SET JsonString = :JsonCfg WHERE WayUid = 1575876387 and ChanCode = :cliID";
    m_query.prepare(sql_JsonCfg);
    m_query.bindValue(":cliID", cliNum);

    // 将QJsonDocument转换为QString
    QString jsonString = jsonDoc.toJson(QJsonDocument::Indented);
    m_query.bindValue(":JsonCfg", jsonString);
    if(!m_query.exec()){
        qDebug() << "Failed to execute SQL query";
        qDebug() << m_query.lastError().text();
        return -1;
    }

}

int DBControl::handleTopicInfo(QString filePath, QString fileName)
{
    m_query.clear();

    int cliNum = 1;
    QRegularExpression regex("\\d+");
    QRegularExpressionMatchIterator matchIterator = regex.globalMatch(fileName);
    while (matchIterator.hasNext())
    {
        QRegularExpressionMatch match = matchIterator.next();
        QString number = match.captured();
        cliNum = number.toInt();
    }

    QString topicName = "AMS" + QString::number(cliNum);
    qDebug() << topicName;

    QFile file(filePath);

    if(!file.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        qDebug() << "Failed to open JSON file";
        return -1;
    }

    // 创建一个QTextStream对象，并将其与QFile对象关联
    QTextStream in(&file);

    // 读取文件内容
    QString content = in.readAll();
    file.close();

    // qDebug() << content;
    QString sql_Topic = "UPDATE topicinfo SET IndexSet = :TopicIndex WHERE TopicName = :TopicName";
    m_query.prepare(sql_Topic);
    m_query.bindValue(":TopicIndex", content);
    m_query.bindValue(":TopicName", topicName);
    if(!m_query.exec()){
        qDebug() << "Failed to execute SQL query " << __LINE__;
        qDebug() << m_query.lastError().text();
        return -1;
    }

    m_query.clear();
    m_query.prepare("INSERT INTO hldkeymap(orderkey, KeyValue, desc_cn, unit, TopicName) VALUES(:orderkey, :KeyValue, :desc_cn, :unit, :TopicName)");
    QStringList strList = content.split(",");
    for(auto item : strList)
    {
        if(item == ""){
            continue;
        }

        m_query.bindValue(":orderkey", ams_orderKey);
        m_query.bindValue(":KeyValue", item);
        m_query.bindValue(":desc_cn", "--");
        m_query.bindValue(":unit", "");
        m_query.bindValue(":TopicName", topicName);

        if(m_query.exec())
        {
            // qDebug() << "Data inserted successfully. ^_^ --> " << orderKey;
        }
        else{
            qDebug() << "Failed to insert data.";
        }

        ams_orderKey++;
    }
}

int DBControl::msgPointDesc2DB()
{
    QXlsx::Document xlsx("./ams/AMS_Details.xlsx");
    QXlsx::CellRange range = xlsx.dimension();
    qDebug() << "RowCount: " << range.lastRow() << "; ColumnCount: " <<  range.lastColumn();

    m_query.prepare("UPDATE hldkeymap SET desc_cn = :desc, unit = :unit WHERE orderkey = :orderKey");

    int orderKey = 51;
    QXlsx::Worksheet* sheet = xlsx.currentWorksheet();
    if(sheet)
    {
        for(int i = 3; i <= sheet->dimension().rowCount(); i++)
        {
            for(int j = 1; j <= sheet->dimension().columnCount(); j++)
            {
                if(j == 1){
                    m_query.bindValue(":orderKey", orderKey);
                }
                else if(j == 2)
                {
                    m_query.bindValue(":desc", sheet->read(i, j).toString());
                }
                else if(j == 3)
                {
                    m_query.bindValue(":unit", sheet->read(i, j).toString());
                }
            }

            if(m_query.exec())
            {
                // qDebug() << "UPDATE inserted successfully. -> " << i;
            }
            else{
                qDebug() << "Failed to insert data " << __LINE__;
            }

            orderKey++;
        }
    }
}

